package com.lambkit.db.sql;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import com.lambkit.db.meta.JavaType;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

public class SqlConcat {

    private JavaType javaTypeMap = null;

    public JavaType getJavaTypeMap() {
        return javaTypeMap;
    }

    public void setJavaTypeMap(JavaType javaTypeMap) {
        this.javaTypeMap = javaTypeMap;
    }

    /**
     * 单column字段列的SQL查询条件生成
     * @param columns
     * @param field
     * @param value
     * @param type
     * @param javaType
     * @return
     */
    public Columns column(Columns columns, String field, String value, String type, boolean javaType) {
        if(StrUtil.isNotBlank(value)) {
            String info = value.trim();
            //Printer.print(this, "db"("Field: " + field + ", info: " + info);
            if(info.startsWith("{") && info.endsWith("}")) {
                info = info.substring(1, info.length()-1);
                if("empty".equalsIgnoreCase(info)) {
                    columns.empty(field);
                } else if("not_empty".equalsIgnoreCase(info)) {
                    columns.notEmpty(field);
                } else if("isnull".equalsIgnoreCase(info)) {
                    columns.isnull(field);
                } else if("not_null".equalsIgnoreCase(info)) {
                    columns.notNull(field);
                }
            } else if(info.startsWith("@")) {
                //包含
                info = info.substring(1).trim();
                if(info.startsWith("-") || info.startsWith(",")) {
                    info = info.substring(1);
                }
                if(info.endsWith("-") || info.endsWith(",")) {
                    info = info.substring(0, info.length()-1);
                }
                if(info.contains("%")) {
                    columns.like(field, info);
                } else if(info.startsWith("=")) {
                    columns.eq(field, getValue(type, info.substring(1), javaType));
                } else if(info.contains("-")) {
                    String[] infos = info.split("-");
                    columns.between(field, getValue(type, infos[0], javaType), getValue(type, infos[1], javaType));
                } else if(info.contains(",") && !info.endsWith(",") && !info.endsWith(",%")) {
                    String[] infos = info.split(",");
                    if(infos.length > 0) {
                        columns.in(field, transArrayValue(type, infos, javaType));
                    }
                } else {
                    columns.like(field, info);
                }
            } else if(info.startsWith("#")) {
                //不包含
                info = info.substring(1).trim();
                if(info.startsWith("-") || info.startsWith(",")) {
                    info = info.substring(1);
                }
                if(info.endsWith("-") || info.endsWith(",")) {
                    info = info.substring(0, info.length()-1);
                }
                if(info.contains("%")) {
                    columns.like(field, info);
                } else if(info.startsWith("=")) {
                    columns.ne(field, getValue(type, info.substring(1), javaType));
                } else if(info.contains("-")) {
                    String[] infos = info.split("-");
                    columns.notBetween(field, getValue(type, infos[0], javaType), getValue(type, infos[1], javaType));
                } else if(info.contains(",") && !info.endsWith(",") && !info.endsWith(",%")) {
                    String[] infos = info.split(",");
                    if(infos.length > 0) {
                        columns.notIn(field, transArrayValue(type, infos, javaType));
                    }
                } else {
                    columns.notLike(field, info);
                }
            } else if(info.startsWith("!=")) {
                //info = info.substring(2).trim();
                //if(info.startsWith("$") && info.endsWith("$")) info = info.substring(1, info.length()-1);
                columns.ne(field, getValue(type, info.substring(2), javaType));
            } else if(info.startsWith("<=")) {
                columns.le(field, getValue(type, info.substring(1), javaType));
            } else if(info.startsWith("<")) {
                columns.lt(field, getValue(type, info.substring(2), javaType));
            } else if(info.startsWith(">=")) {
                columns.ge(field, getValue(type, info.substring(1), javaType));
            } else if(info.startsWith(">")) {
                columns.gt(field, getValue(type, info.substring(2), javaType));
            } else if(info.contains("%")) {
                columns.like(field, info);
            } else {
                columns.eq(field, getValue(type, info, javaType));
            }
        }
        return columns;
    }

    /**
     * 查询配置转成SQL查询条件
     * @param columns
     * @param field
     * @param value
     * @param type
     * @param javaType
     * @return
     */
    public Columns filter(Columns columns, String field, String value, String type, boolean javaType) {
        //Printer.print(this, "db"("filter0: " + value);
        if(StrUtil.isNotBlank(value)) {
            String info = value.trim();
            if(info.startsWith("$")) {
                columns.add(Column.createRefColumn(field, value));
                columns.addRef(field);
            } else if(info.startsWith("@")) {
                return column(columns, field, value, type, javaType);
            } else if(info.startsWith("#")) {
                return column(columns, field, value, type, javaType);
            } else if(info.startsWith("!")) {
                info = info.substring(1).trim();
                //not
                if(info.startsWith("{") && info.endsWith("}")) {
                    info = info.substring(1, info.length()-1);
                    //null or empty
                    if("empty".equalsIgnoreCase(info)) {
                        columns.notEmpty(field);
                    } else if("null".equalsIgnoreCase(info)) {
                        columns.notNull(field);
                    }
                } else if(info.startsWith("[") && info.endsWith("]")) {
                    //list
                    info = info.substring(1, info.length()-1);
                    String[] infos = info.split(",");
                    if(infos.length > 0) {
                        columns.notIn(field, transArrayValue(type, infos, javaType));
                    }
                } else if(info.startsWith("(") && info.endsWith(")")) {
                    //多个过滤条件
                    info = info.substring(1, info.length()-1);
                    ColumnsGroup colg = new ColumnsGroup();
                    SqlConcatGroup sqlConcatGroup = new SqlConcatGroup();
                    sqlConcatGroup.filterGroup(colg, field, info, type, 0, false);
                    colg.columnsWithAnd(columns);
                    return colg;
                } else {
                    if(info.contains("%")) {
                        String stType = getStType(type, javaType);
                        if("string".equals(stType) || "date".equals(stType)
                                || String.class.getName().equals(stType)
                                || Date.class.getName().equals(stType)
                                || Timestamp.class.getName().equals(stType)) {
                            columns.notLike(field, info);
                        } else {
                            columns.addWarn(field + " (" + type + ") cannot use like " + info);
                        }
                    } else if(info.startsWith("=")) {
                        columns.ne(field, getValue(type, info.substring(1), javaType));
                    } else if(info.contains("~")) {
                        String[] infos = info.split("~");
                        columns.notBetween(field, getValue(type, infos[0], javaType), getValue(type, infos[1], javaType));
//					} else if(info.contains(",") && !info.endsWith(",") && !info.endsWith(",%")) {
//						String[] infos = info.split(",");
//						if(infos.length > 0) {
//							notIn(field, transArrayValue(type, infos));
//						}
                    } else {
                        columns.ne(field, info);
                    }
                }
            } else {
                if(info.startsWith("{") && info.endsWith("}")) {
                    //null or empty
                    info = info.substring(1, info.length()-1);
                    if("empty".equalsIgnoreCase(info)) {
                        columns.empty(field);
                    } else if("null".equalsIgnoreCase(info)) {
                        columns.isnull(field);
                    }
                } else if(info.startsWith("[") && info.endsWith("]")) {
                    //list
                    info = info.substring(1, info.length()-1);
                    String[] infos = info.split(",");
                    if(infos.length > 0) {
                        //Printer.print(this, "db"("list filter: " + field + "=" + info);
                        columns.in(field, transArrayValue(type, infos, javaType));
                    }
                } else if(info.startsWith("(") && info.endsWith(")")) {
                    //多个过滤条件
                    info = info.substring(1, info.length()-1);
                    ColumnsGroup colg = new ColumnsGroup();
                    SqlConcatGroup sqlConcatGroup = new SqlConcatGroup();
                    sqlConcatGroup.filterGroup(colg, field, info, type, 0, false);
                    colg.columnsWithAnd(columns);
                    return colg;
                } else {
                    if(info.contains("%")) {
                        String stType = getStType(type, javaType);
                        if("string".equals(stType) || "date".equals(stType)
                                || String.class.getName().equals(stType)
                                || Date.class.getName().equals(stType)
                                || Timestamp.class.getName().equals(stType)) {
                            columns.like(field, info);
                        } else {
                            columns.addWarn(field + " (" + type + ") cannot use like " + info);
                        }
                    } else if(info.startsWith("=")) {
                        columns.eq(field, getValue(type, info.substring(1), javaType));
                    } else if(info.contains("~")) {
                        String[] infos = info.split("~");
                        columns.between(field, getValue(type, infos[0], javaType), getValue(type, infos[1], javaType));
//					} else if(info.contains(",") && !info.endsWith(",") && !info.endsWith(",%")) {
//						String[] infos = info.split(",");
//						if(infos.length > 0) {
//							in(field, transArrayValue(type, infos));
//						}
                    } else if(info.startsWith("<=")) {
                        columns.le(field, getValue(type, info.substring(1), javaType));
                    } else if(info.startsWith("<")) {
                        columns.lt(field, getValue(type, info.substring(2), javaType));
                    } else if(info.startsWith(">=")) {
                        columns.ge(field, getValue(type, info.substring(1), javaType));
                    } else if(info.startsWith(">")) {
                        columns.gt(field, getValue(type, info.substring(2), javaType));
                    } else {
                        columns.eq(field, getValue(type, info, javaType));
                    }
                }
            }
        }
        return columns;
    }

    protected String getStType(String type, boolean javaType) {
        if(javaType) {
            return type;
        }
        type = type.toLowerCase();
        if(type.startsWith("bigint") || type.startsWith("bigserial") || type.startsWith("int8") || "int unsigned".equals(type)) {
            return "long";
        }
        else if(type.startsWith("int") || type.startsWith("serial") || type.startsWith("tinyint")) {
            return "int";
        }
        else if(type.startsWith("float")) {
            return "double";
        }
        else if(type.startsWith("double")) {
            return "double";
        }
        else if(type.startsWith("num")) {
            return "double";//numeric,number
        }
        else if(type.startsWith("date")) {
            return "date";
        }
        else if(type.startsWith("datetime")) {
            return "date";
        }
        else if(type.startsWith("timestamp")) {
            return "date";
        }
        else {
            return "string";
        }
    }



    protected Object getValue(String type, String value, boolean javaType) {
        if(javaType) {
            if(javaTypeMap==null) {
                javaTypeMap = new JavaType();
            }
            Class<?> colType = javaTypeMap.getType(type);
            if(colType!=null) {
                return StrUtil.isNotBlank(value) ? Convert.convert(colType, value) : null;
            }
            return value;
        }
        type = type.toLowerCase();
        if(type.startsWith("bigint") || type.startsWith("bigserial") || type.startsWith("int8") || "int unsigned".equals(type)) {
            return Long.parseLong(value);
        }
        else if(type.startsWith("int") || type.startsWith("serial") || type.startsWith("tinyint")) {
            return Integer.parseInt(value);
        }
        else if(type.startsWith("float")) {
            return Float.parseFloat(value);
        }
        else if(type.startsWith("double")) {
            return Double.parseDouble(value);
        }
        else if(type.startsWith("num")) {
            return Double.parseDouble(value);//numeric,number
        }
        else if(type.startsWith("date")) {
            return Date.valueOf(value);
        }
        else if(type.startsWith("datetime")) {
            return Timestamp.valueOf(value);
        }
        else if(type.startsWith("timestamp")) {
            return Timestamp.valueOf(value);
        }
        else {
            return value;
        }
    }

    protected Object transArrayValue(String type, String[] value, boolean javaType) {
        if(javaType) {
            if(value.length > 0) {
                List<Object> result = CollUtil.newArrayList();
                for(int i=0; i<value.length; i++) {
                    String val = value[i];
                    Object oval = getValue(type, val, javaType);
                    result.add(oval);
                }
                return result;
            }
            return java.util.Arrays.asList(value);
        }
        //判断字段类型
        type = type.toLowerCase();
        if(type.startsWith("bigint") || type.startsWith("bigserial") || type.startsWith("int8") || "int unsigned".equals(type)) {
            return StringToLong(value);
        }
        else if(type.startsWith("int") || type.startsWith("tinyint")) {
            return StringToInt(value);
        }
        else if(type.startsWith("float")) {
            return StringToFloat(value);
        }
        else if(type.startsWith("double")) {
            return StringToDouble(value);
        }
        else if(type.startsWith("num")) {
            return StringToDouble(value);//numeric,number
        }
        else if(type.startsWith("date")) {
            return StringToDate(value);
        }
        else if(type.startsWith("datetime")) {
            return StringToTimestamp(value);
        }
        return java.util.Arrays.asList(value);
    }

    private List<Integer> StringToInt(String[] arrs){
        List<Integer> ints = new ArrayList<Integer>();
        for(int i=0;i<arrs.length;i++){
            ints.add(Integer.parseInt(arrs[i]));
        }
        return ints;
    }

    private List<Long> StringToLong(String[] arrs){
        List<Long> ints = new ArrayList<Long>();
        for(int i=0;i<arrs.length;i++){
            ints.add(Long.parseLong(arrs[i]));
        }
        return ints;
    }

    private List<Float> StringToFloat(String[] arrs){
        List<Float> ints = new ArrayList<Float>();
        for(int i=0;i<arrs.length;i++){
            ints.add(Float.parseFloat(arrs[i]));
        }
        return ints;
    }

    private List<Double> StringToDouble(String[] arrs){
        List<Double> ints = new ArrayList<Double>();
        for(int i=0;i<arrs.length;i++){
            ints.add(Double.parseDouble(arrs[i]));
        }
        return ints;
    }

    private List<Date> StringToDate(String[] arrs){
        List<Date> ints = new ArrayList<Date>();
        for(int i=0;i<arrs.length;i++){
            ints.add(Date.valueOf(arrs[i]));
        }
        return ints;
    }

    private List<Timestamp> StringToTimestamp(String[] arrs){
        List<Timestamp> ints = new ArrayList<Timestamp>();
        for(int i=0;i<arrs.length;i++){
            ints.add(Timestamp.valueOf(arrs[i]));
        }
        return ints;
    }
//
// 	private int[] StringToInt(String[] arrs){
// 	    int[] ints = new int[arrs.length];
// 	    for(int i=0;i<arrs.length;i++){
// 	        ints[i] = Integer.parseInt(arrs[i]);
// 	    }
// 	    return ints;
// 	}
//
// 	private float[] StringToFloat(String[] arrs){
// 		float[] ints = new float[arrs.length];
// 	    for(int i=0;i<arrs.length;i++){
// 	        ints[i] = Float.parseFloat(arrs[i]);
// 	    }
// 	    return ints;
// 	}
//
// 	private double[] StringToDouble(String[] arrs){
// 		double[] ints = new double[arrs.length];
// 	    for(int i=0;i<arrs.length;i++){
// 	        ints[i] = Double.parseDouble(arrs[i]);
// 	    }
// 	    return ints;
// 	}
//
// 	private Date[] StringToDate(String[] arrs){
// 		Date[] ints = new Date[arrs.length];
// 	    for(int i=0;i<arrs.length;i++){
// 	        ints[i] = Date.valueOf(arrs[i]);
// 	    }
// 	    return ints;
// 	}
//
// 	private Timestamp[] StringToTimestamp(String[] arrs){
// 		Timestamp[] ints = new Timestamp[arrs.length];
// 	    for(int i=0;i<arrs.length;i++){
// 	        ints[i] = Timestamp.valueOf(arrs[i]);
// 	    }
// 	    return ints;
// 	}
}
